package com.gpzx.zx.utlis;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import com.gpzx.zx.module.home.frame.model.QKLNewsInfo;
import com.gpzx.zx.module.home.frame.model.QKLTagInfo;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 作者:吴奎庆
 * <p>
 * 时间:2020/7/13
 * <p>
 * 用途:
 */


public class QklDbUtlis extends SQLiteOpenHelper {
    private static String DB_PATH = "data/data/com.gpzx.zx/databases/";
    private static String DB_NAME = "q_news";
    String path = DB_PATH + DB_NAME;
    String sevenTableName = "qkl_seven_news";
    String tagTableName = "qkl_news_tag";
    String newsTableName = "qkl_news";
    String authorTableName = "qkl_author_news";
    private SQLiteDatabase dbObj;

    public final Context context;

    public QklDbUtlis(Context context) {
        super(context, DB_NAME, null, 3);
        this.context = context;
        try {
            if (!checkDB()) copyDB();
            dbObj = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 检测数据库是否存在
     *
     * @return
     */
    private boolean checkDB() {

        SQLiteDatabase checkDB = null;
        try {
            checkDB = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
            if (checkDB != null) {
            } else {
                return false;
            }

        } catch (SQLiteException e) {
            e.printStackTrace();
        }
        if (checkDB != null) {
            checkDB.close();
        }
        return checkDB != null ? true : false;
    }


    /**
     * 见assets目录下的文件拷贝到sd上
     *
     * @return 存储数据库的地址
     */

    public void copyDB() throws IOException {
        InputStream ip = context.getAssets().open(DB_NAME + ".sqlite3");
        File dir = new File(DB_PATH);
        if (!dir.exists() || !dir.isDirectory()) {
            dir.mkdir();
        }
        File file = new File(dir, DB_NAME);
        if (!file.exists()) {
            file.createNewFile();
        }
        try {
            OutputStream output = new FileOutputStream(file);
            byte[] buffer = new byte[1024];
            int length;
            while ((length = ip.read(buffer)) > 0) {
                output.write(buffer, 0, length);
            }
            output.flush();
            output.close();
            ip.close();
        } catch (Exception e) {
            ip.close();
        }

    }

    public SQLiteDatabase getDatabase() {
        String myPath = DB_PATH + DB_NAME;
        dbObj = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
        return dbObj;
    }

    public void openDB() {
        try {
            if (dbObj != null && !dbObj.isOpen()) {
                String myPath = DB_PATH + DB_NAME;
                dbObj = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
            }
        } catch (Exception e) {

        }

    }

    @Override
    public synchronized void close() {
        if (dbObj != null)
            dbObj.close();
        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    /**
     * 按类型获取资讯
     * @param type
     * @return
     */
    public List<QKLNewsInfo> getNewsByType(String type) {
        openDB();
        List<QKLNewsInfo> infos = new ArrayList<>();
        String sql = "SELECT * FROM qkl_news where news_type=? group by news_title";
        String[] selectionArgs = new String[]{type};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            QKLNewsInfo info = new QKLNewsInfo();

            String id = cursor.getString(cursor.getColumnIndex("id"));
            String news_id = cursor.getString(cursor.getColumnIndex("news_id"));
            String news_author_name = cursor.getString(cursor.getColumnIndex("news_author_name"));
            String news_author_id = cursor.getString(cursor.getColumnIndex("news_author_id"));
            String news_author_icon = cursor.getString(cursor.getColumnIndex("news_author_icon"));
            String news_author_desc = cursor.getString(cursor.getColumnIndex("news_author_desc"));
//            String news_author_support = cursor.getString(cursor.getColumnIndex("news_author_support"));
//            String news_author_funs = cursor.getString(cursor.getColumnIndex("news_author_funs"));
//            String news_author_news = cursor.getString(cursor.getColumnIndex("news_author_news"));
            String news_watch = cursor.getString(cursor.getColumnIndex("news_watch"));
            String news_time = cursor.getString(cursor.getColumnIndex("news_time"));
            String news_title = cursor.getString(cursor.getColumnIndex("news_title"));
            String news_desc = cursor.getString(cursor.getColumnIndex("news_desc"));
            String news_type = cursor.getString(cursor.getColumnIndex("news_type"));
            String news_detail = cursor.getString(cursor.getColumnIndex("news_detail"));
            String news_icon = cursor.getString(cursor.getColumnIndex("news_icon"));

            info.setId(id);
            info.setNews_id(news_id);
            info.setNews_author_id(news_author_id);
            info.setNews_author_desc(news_author_desc);
            info.setNews_author_name(news_author_name);
            info.setNews_author_icon(news_author_icon);
//            info.setNews_author_funs(news_author_funs);
//            info.setNews_author_news(news_author_news);
//            info.setNews_author_support(news_author_support);

            info.setNews_watch(news_watch);
            info.setNews_desc(news_desc);
            info.setNews_detail(news_detail);
            info.setNews_icon(news_icon);
            info.setNews_time(news_time);
            info.setNews_type(news_type);
            info.setNews_title(news_title);
            infos.add(info);

        }
        return infos;
    }

    /**
     * 获取资讯详情
     *
     * @param
     * @return
     */
    public QKLNewsInfo getNewsDetail(String ids) {
        openDB();

        String sql = "SELECT * FROM qkl_news where id=?";
        String[] selectionArgs = new String[]{ids};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        QKLNewsInfo info = new QKLNewsInfo();
        while (cursor.moveToNext()) {


            String id = cursor.getString(cursor.getColumnIndex("id"));
            String news_id = cursor.getString(cursor.getColumnIndex("news_id"));
            String news_author_name = cursor.getString(cursor.getColumnIndex("news_author_name"));
            String news_author_id = cursor.getString(cursor.getColumnIndex("news_author_id"));
            String news_author_icon = cursor.getString(cursor.getColumnIndex("news_author_icon"));
            String news_author_desc = cursor.getString(cursor.getColumnIndex("news_author_desc"));
//            String news_author_support = cursor.getString(cursor.getColumnIndex("news_author_support"));
//            String news_author_funs = cursor.getString(cursor.getColumnIndex("news_author_funs"));
//            String news_author_news = cursor.getString(cursor.getColumnIndex("news_author_news"));
            String news_watch = cursor.getString(cursor.getColumnIndex("news_watch"));
            String news_time = cursor.getString(cursor.getColumnIndex("news_time"));
            String news_title = cursor.getString(cursor.getColumnIndex("news_title"));
            String news_desc = cursor.getString(cursor.getColumnIndex("news_desc"));
            String news_type = cursor.getString(cursor.getColumnIndex("news_type"));
            String news_detail = cursor.getString(cursor.getColumnIndex("news_detail"));
            String news_icon = cursor.getString(cursor.getColumnIndex("news_icon"));

            info.setId(id);
            info.setNews_id(news_id);
            info.setNews_author_id(news_author_id);
            info.setNews_author_desc(news_author_desc);
            info.setNews_author_name(news_author_name);
            info.setNews_author_icon(news_author_icon);
//            info.setNews_author_funs(news_author_funs);
//            info.setNews_author_news(news_author_news);
//            info.setNews_author_support(news_author_support);

            info.setNews_watch(news_watch);
            info.setNews_desc(news_desc);
            info.setNews_detail(news_detail);
            info.setNews_icon(news_icon);
            info.setNews_time(news_time);
            info.setNews_type(news_type);
            info.setNews_title(news_title);


        }
        return info;
    }

    /**
     * 获取 7x24小时新闻
     *
     * @return
     */
    public List<QKLNewsInfo> get7X24News() {
        openDB();
        List<QKLNewsInfo> infos = new ArrayList<>();
        String sql = "SELECT * FROM qkl_seven_news";
//        String[] selectionArgs = new String[]{type};
        Cursor cursor = dbObj.rawQuery(sql, null);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            QKLNewsInfo info = new QKLNewsInfo();

            String id = cursor.getString(cursor.getColumnIndex("id"));
            String news_id = cursor.getString(cursor.getColumnIndex("news_id"));
//            String news_author_name = cursor.getString(cursor.getColumnIndex("news_author_name"));
//            String news_author_id = cursor.getString(cursor.getColumnIndex("news_author_id"));
//            String news_author_icon = cursor.getString(cursor.getColumnIndex("news_author_icon"));
//            String news_author_desc = cursor.getString(cursor.getColumnIndex("news_author_desc"));
//            String news_author_support = cursor.getString(cursor.getColumnIndex("news_author_support"));
//            String news_author_funs = cursor.getString(cursor.getColumnIndex("news_author_funs"));
//            String news_author_news = cursor.getString(cursor.getColumnIndex("news_author_news"));
//            String news_watch = cursor.getString(cursor.getColumnIndex("news_watch"));
            String news_time = cursor.getString(cursor.getColumnIndex("news_time"));
            String news_title = cursor.getString(cursor.getColumnIndex("news_title"));
            String news_desc = cursor.getString(cursor.getColumnIndex("news_desc"));
            String news_type = cursor.getString(cursor.getColumnIndex("news_type"));
            String news_detail = cursor.getString(cursor.getColumnIndex("news_detail"));
//            String news_icon = cursor.getString(cursor.getColumnIndex("news_icon"));
            String news_support_no = cursor.getString(cursor.getColumnIndex("news_support_no"));
            String news_support = cursor.getString(cursor.getColumnIndex("news_support"));

            info.setId(id);
            info.setNews_id(news_id);
//            info.setNews_author_id(news_author_id);
//            info.setNews_author_desc(news_author_desc);
//            info.setNews_author_name(news_author_name);
//            info.setNews_author_icon(news_author_icon);
//            info.setNews_author_funs(news_author_funs);
//            info.setNews_author_news(news_author_news);
//            info.setNews_author_support(news_author_support);

//            info.setNews_watch(news_watch);
            info.setNews_desc(news_desc);
            info.setNews_detail(news_detail);
//            info.setNews_icon(news_icon);
            info.setNews_time(news_time);
            info.setNews_type(news_type);
            info.setNews_title(news_title);
            info.setNews_support(news_support);
            info.setNews_support_no(news_support_no);
            infos.add(info);
        }
        return infos;
    }

    /**
     * 获取 7x24 的详细信息
     *
     * @param newsId
     * @return
     */
    public QKLNewsInfo get7X24NewsDetail(String newsId) {
        openDB();
        String sql = "SELECT * FROM qkl_seven_news where news_id=?";
        String[] selectionArgs = new String[]{newsId};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        QKLNewsInfo info = new QKLNewsInfo();
        while (cursor.moveToNext()) {


            String id = cursor.getString(cursor.getColumnIndex("id"));
            String news_id = cursor.getString(cursor.getColumnIndex("news_id"));

            String news_time = cursor.getString(cursor.getColumnIndex("news_time"));
            String news_title = cursor.getString(cursor.getColumnIndex("news_title"));
            String news_desc = cursor.getString(cursor.getColumnIndex("news_desc"));
            String news_type = cursor.getString(cursor.getColumnIndex("news_type"));
            String news_detail = cursor.getString(cursor.getColumnIndex("news_detail"));
            String news_support_no = cursor.getString(cursor.getColumnIndex("news_support_no"));
            String news_support = cursor.getString(cursor.getColumnIndex("news_support"));

            info.setId(id);
            info.setNews_id(news_id);
            info.setNews_desc(news_desc);
            info.setNews_detail(news_detail);
            info.setNews_time(news_time);
            info.setNews_type(news_type);
            info.setNews_title(news_title);
            info.setNews_support(news_support);
            info.setNews_support_no(news_support_no);

        }
        return info;
    }

    /**
     * 获取 所有作者信息
     *
     * @return
     */
    public List<QKLNewsInfo> get7AuthorNews() {
        openDB();
        List<QKLNewsInfo> infos = new ArrayList<>();
        // group 分组
        String sql = "SELECT * FROM qkl_author_news group by news_author_name";
        Cursor cursor = dbObj.rawQuery(sql, null);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            QKLNewsInfo info = new QKLNewsInfo();

            String id = cursor.getString(cursor.getColumnIndex("id"));
            String news_id = cursor.getString(cursor.getColumnIndex("news_id"));
            String news_author_name = cursor.getString(cursor.getColumnIndex("news_author_name"));
            String news_author_id = cursor.getString(cursor.getColumnIndex("news_author_id"));
            String news_author_icon = cursor.getString(cursor.getColumnIndex("news_author_icon"));
            String news_author_desc = cursor.getString(cursor.getColumnIndex("news_author_desc"));
            String news_author_support = cursor.getString(cursor.getColumnIndex("news_author_support"));
            String news_author_funs = cursor.getString(cursor.getColumnIndex("news_author_funs"));
            String news_author_news = cursor.getString(cursor.getColumnIndex("news_author_news"));
            String news_watch = cursor.getString(cursor.getColumnIndex("news_watch"));
            String news_time = cursor.getString(cursor.getColumnIndex("news_time"));
            String news_title = cursor.getString(cursor.getColumnIndex("news_title"));
            String news_desc = cursor.getString(cursor.getColumnIndex("news_desc"));
            String news_type = cursor.getString(cursor.getColumnIndex("news_type"));
            String news_detail = cursor.getString(cursor.getColumnIndex("news_detail"));
            String news_icon = cursor.getString(cursor.getColumnIndex("news_icon"));


            info.setId(id);
            info.setNews_id(news_id);
            info.setNews_author_id(news_author_id);
            info.setNews_author_desc(news_author_desc);
            info.setNews_author_name(news_author_name);
            info.setNews_author_icon(news_author_icon);
            info.setNews_author_funs(news_author_funs);
            info.setNews_author_news(news_author_news);
            info.setNews_author_support(news_author_support);

            info.setNews_watch(news_watch);
            info.setNews_desc(news_desc);
            info.setNews_detail(news_detail);
            info.setNews_icon(news_icon);
            info.setNews_time(news_title);
            info.setNews_type(news_type);
            info.setNews_title(news_title);

            infos.add(info);
        }
        return infos;
    }
    /**
     * 获取 所有作者新闻
     *
     * @return
     */
    public List<QKLNewsInfo> getAllAuthorNews() {
        openDB();
        List<QKLNewsInfo> infos = new ArrayList<>();
        // group 分组
        String sql = "SELECT * FROM qkl_author_news ";
        Cursor cursor = dbObj.rawQuery(sql, null);
        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        while (cursor.moveToNext()) {
            QKLNewsInfo info = new QKLNewsInfo();

            String id = cursor.getString(cursor.getColumnIndex("id"));
            String news_id = cursor.getString(cursor.getColumnIndex("news_id"));
            String news_author_name = cursor.getString(cursor.getColumnIndex("news_author_name"));
            String news_author_id = cursor.getString(cursor.getColumnIndex("news_author_id"));
            String news_author_icon = cursor.getString(cursor.getColumnIndex("news_author_icon"));
            String news_author_desc = cursor.getString(cursor.getColumnIndex("news_author_desc"));
            String news_author_support = cursor.getString(cursor.getColumnIndex("news_author_support"));
            String news_author_funs = cursor.getString(cursor.getColumnIndex("news_author_funs"));
            String news_author_news = cursor.getString(cursor.getColumnIndex("news_author_news"));
            String news_watch = cursor.getString(cursor.getColumnIndex("news_watch"));
            String news_time = cursor.getString(cursor.getColumnIndex("news_time"));
            String news_title = cursor.getString(cursor.getColumnIndex("news_title"));
            String news_desc = cursor.getString(cursor.getColumnIndex("news_desc"));
            String news_type = cursor.getString(cursor.getColumnIndex("news_type"));
            String news_detail = cursor.getString(cursor.getColumnIndex("news_detail"));
            String news_icon = cursor.getString(cursor.getColumnIndex("news_icon"));


            info.setId(id);
            info.setNews_id(news_id);
            info.setNews_author_id(news_author_id);
            info.setNews_author_desc(news_author_desc);
            info.setNews_author_name(news_author_name);
            info.setNews_author_icon(news_author_icon);
            info.setNews_author_funs(news_author_funs);
            info.setNews_author_news(news_author_news);
            info.setNews_author_support(news_author_support);

            info.setNews_watch(news_watch);
            info.setNews_desc(news_desc);
            info.setNews_detail(news_detail);
            info.setNews_icon(news_icon);
            info.setNews_time(news_title);
            info.setNews_type(news_type);
            info.setNews_title(news_title);

            infos.add(info);
        }
        return infos;
    }
    /**
     * 获取指定作者的信息
     *
     * @return
     */
    public List<QKLNewsInfo> get7AuthorNews(String authorId) {
        openDB();
        List<QKLNewsInfo> infos = new ArrayList<>();
//       DISTINCT  不重复字段
        String sql = "SELECT * FROM qkl_author_news where news_author_id=? ORDER BY id";
        String[] selectionArgs = new String[]{authorId};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);

        while (cursor.moveToNext()) {
            QKLNewsInfo info = new QKLNewsInfo();

            String id = cursor.getString(cursor.getColumnIndex("id"));
            String news_id = cursor.getString(cursor.getColumnIndex("news_id"));
            String news_author_name = cursor.getString(cursor.getColumnIndex("news_author_name"));
            String news_author_id = cursor.getString(cursor.getColumnIndex("news_author_id"));
            String news_author_icon = cursor.getString(cursor.getColumnIndex("news_author_icon"));
            String news_author_desc = cursor.getString(cursor.getColumnIndex("news_author_desc"));
            String news_author_support = cursor.getString(cursor.getColumnIndex("news_author_support"));
            String news_author_funs = cursor.getString(cursor.getColumnIndex("news_author_funs"));
            String news_author_news = cursor.getString(cursor.getColumnIndex("news_author_news"));
            String news_watch = cursor.getString(cursor.getColumnIndex("news_watch"));
            String news_time = cursor.getString(cursor.getColumnIndex("news_time"));
            String news_title = cursor.getString(cursor.getColumnIndex("news_title"));
            String news_desc = cursor.getString(cursor.getColumnIndex("news_desc"));
            String news_type = cursor.getString(cursor.getColumnIndex("news_type"));
            String news_detail = cursor.getString(cursor.getColumnIndex("news_detail"));
            String news_icon = cursor.getString(cursor.getColumnIndex("news_icon"));


            info.setId(id);
            info.setNews_id(news_id);
            info.setNews_author_id(news_author_id);
            info.setNews_author_desc(news_author_desc);
            info.setNews_author_name(news_author_name);
            info.setNews_author_icon(news_author_icon);
            info.setNews_author_funs(news_author_funs);
            info.setNews_author_news(news_author_news);
            info.setNews_author_support(news_author_support);

            info.setNews_watch(news_watch);
            info.setNews_desc(news_desc);
            info.setNews_detail(news_detail);
            info.setNews_icon(news_icon);
            info.setNews_type(news_type);
            info.setNews_time(news_time);
            info.setNews_title(news_title);

            infos.add(info);
        }
        return infos;
    }

    /**
     * 获取指定作者的信息
     *
     * @return
     */
    public QKLNewsInfo getAuthorNewsByID(String newsId) {
        openDB();
//       DISTINCT  不重复字段
        String sql = "SELECT * FROM qkl_author_news where id=? ORDER BY id";
        String[] selectionArgs = new String[]{newsId};
        Cursor cursor = dbObj.rawQuery(sql, selectionArgs);
        QKLNewsInfo info = new QKLNewsInfo();
        while (cursor.moveToNext()) {


            String id = cursor.getString(cursor.getColumnIndex("id"));
            String news_id = cursor.getString(cursor.getColumnIndex("news_id"));
            String news_author_name = cursor.getString(cursor.getColumnIndex("news_author_name"));
            String news_author_id = cursor.getString(cursor.getColumnIndex("news_author_id"));
            String news_author_icon = cursor.getString(cursor.getColumnIndex("news_author_icon"));
            String news_author_desc = cursor.getString(cursor.getColumnIndex("news_author_desc"));
            String news_author_support = cursor.getString(cursor.getColumnIndex("news_author_support"));
            String news_author_funs = cursor.getString(cursor.getColumnIndex("news_author_funs"));
            String news_author_news = cursor.getString(cursor.getColumnIndex("news_author_news"));
            String news_watch = cursor.getString(cursor.getColumnIndex("news_watch"));
            String news_time = cursor.getString(cursor.getColumnIndex("news_time"));
            String news_title = cursor.getString(cursor.getColumnIndex("news_title"));
            String news_desc = cursor.getString(cursor.getColumnIndex("news_desc"));
            String news_type = cursor.getString(cursor.getColumnIndex("news_type"));
            String news_detail = cursor.getString(cursor.getColumnIndex("news_detail"));
            String news_icon = cursor.getString(cursor.getColumnIndex("news_icon"));


            info.setId(id);
            info.setNews_id(news_id);
            info.setNews_author_id(news_author_id);
            info.setNews_author_desc(news_author_desc);
            info.setNews_author_name(news_author_name);
            info.setNews_author_icon(news_author_icon);
            info.setNews_author_funs(news_author_funs);
            info.setNews_author_news(news_author_news);
            info.setNews_author_support(news_author_support);
            info.setNews_watch(news_watch);
            info.setNews_desc(news_desc);
            info.setNews_detail(news_detail);
            info.setNews_icon(news_icon);
            info.setNews_type(news_type);
            info.setNews_time(news_time);
            info.setNews_title(news_title);
        }

        return info;
    }

    /**
     * 获取指定作者的信息
     *
     * @return
     */
    public List<QKLTagInfo> getTags() {
        openDB();
        List<QKLTagInfo> infos = new ArrayList<>();
//       DISTINCT  不重复字段
        String sql = "SELECT * FROM qkl_news_tag ORDER BY id";
        Cursor cursor = dbObj.rawQuery(sql, null);

        while (cursor.moveToNext()) {
            QKLTagInfo info = new QKLTagInfo();
            String id = cursor.getString(cursor.getColumnIndex("id"));
            String tag_name = cursor.getString(cursor.getColumnIndex("tag_name"));
            info.setId(id);
            info.setTagName(tag_name);
            infos.add(info);
        }
        return infos;
    }

}
